﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using System.Collections;
using Components;

namespace MsDAL
{
    public class CommonDAL
    {
        /// <summary>
        /// 根据表明得到字段的详细信息
        /// </summary>
        /// <param name="tableName"></param>
        public DataTable GetDetailByTableName(string tableName)
        {
            string sql = "SELECT "
                    + " [TableName] = OBJECT_NAME(c.object_id), "
                    + " [ColumnName] = c.name, "
                    + " [Description] = ex.value,"
                    + " [maxlength] = CASE WHEN b.name=\'nchar\' THEN C.max_length/2 WHEN b.name=\'nvarchar\' THEN C.max_length/2 ELSE C.max_length END,"
                    + " TypeName = b.name ,"
                    + " nullable  = is_nullable,"
                    + " [sortid] = column_id"
                    + " FROM "
                    + " sys.columns c "
                    + " LEFT OUTER JOIN "
                    + " sys.extended_properties ex "
                    + " ON "
                    + " ex.major_id = c.object_id "
                    + " AND ex.minor_id = c.column_id "
                    + " AND ex.name = 'MS_Description' "
                    + " INNER JOIN "
                    + " systypes b"
                    + " ON c.user_type_id = b.xusertype "
                    + " WHERE "
                    + " OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 "
                    + " AND OBJECT_NAME(c.object_id) = @tbName "
                    + " ORDER "
                    + " BY OBJECT_NAME(c.object_id), c.column_id";
            SqlParameter p = new SqlParameter("@tbName", tableName);

            return DBHelper.GetData(sql, p);
        }

        /// <summary>
        /// 得到数据库中所有的表
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllTable()
        {
            string sql = "select name from sysobjects where xtype='U' and [name]<>'dtproperties' order by [name]";

            return DBHelper.GetData(sql);
        }

        public string SaveData(List<SqlApp> list)
        {
            return DBHelper.BatchDoSql(list);
        }

        /// <summary>
        /// 公用的分页方法，取得总的记录数
        /// </summary>
        /// <param name="page"></param>
        /// <returns></returns>
        public int GetPageRecoredCount(Pager page)
        {
            DataTable dtRowCount = DBHelper.GetSelectLimit(page);
            if (dtRowCount == null || dtRowCount.Rows.Count == 0)
            {
                return 0;
            }
            return Convert.ToInt32(dtRowCount.Rows[0]["Total"]);
        }

        /// <summary>
        /// 得到分页数据
        /// </summary>
        /// <param name="page"></param>
        /// <returns></returns>
        public DataTable GetPageData(Pager page)
        {
            return DBHelper.GetSelectLimit(page);
        }


        /// <summary>
        /// 加载数据库内设置数据源的方法
        /// </summary>
        /// <param name="page"></param>
        /// <param name="sqlKey">由于可能有表连接，所以可以传一个XML中的key文件过来</param>
        /// <returns></returns>
        public DataTable GetSys5a02Data(string page, string action, string ctl, string where)
        {
            string sql = "sp_loadata";

            SqlParameter[] parameters = {
					new SqlParameter("@pagename",SqlDbType.VarChar),  //这个地方的字段名是数据库中的字段名
					new SqlParameter("@ctlname", SqlDbType.VarChar),
					new SqlParameter("@action", SqlDbType.NVarChar),
					new SqlParameter("@upwhere", SqlDbType.NVarChar)
					};
            parameters[0].Value = page;
            parameters[1].Value = ctl;
            parameters[2].Value = action;
            parameters[3].Value = where;

            return DBHelper.ExecuteProc(sql, parameters);
        }

        /// <summary>
        /// 验证数据库中的数据是否存在
        /// </summary>
        /// <param name="tbName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public bool HasData(string tbName, Hashtable where)
        {
            string sql = string.Format(" SELECT TOP 1 1 FROM {0} WHERE {1} ", tbName, BuildWhere(where));

            return !Validation.IsNull(DBHelper.ExecuteScalar(sql));
        }

        /// <summary>
        /// 根据HashTable得到Where条件
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public string BuildWhere(Hashtable where)
        {
            if (null == where || 0 == where.Count) return " 1 = 1 ";
            StringBuilder sb = new StringBuilder();
            foreach (DictionaryEntry entry in where)
            {
                if (sb.Length != 0) sb.Append(" AND ");

                if (entry.Key.ToString().StartsWith("%")) //不拼接操作符
                {
                    sb.AppendFormat(" {0} {1} ", entry.Key.ToString().Replace("%", ""),
                            Validation.ToString(entry.Value));
                }
                else
                {
                    sb.AppendFormat(" {0} = '{1}' ", entry.Key, Validation.ToString(entry.Value));
                }
            }

            return sb.ToString();
        }

        /// <summary>
        ///  执行数据库的函数
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public DataTable ExecDbFun(string key, Hashtable where)
        {
            string sql = "SELECT fncontent FROM Sys0a80 WHERE fnkey=@fnkey";
            object ct = DBHelper.ExecuteScalar(sql, new SqlParameter("@fnkey", key));
            if (Validation.IsNull(ct)) return null;

            sql = ResetSqlAndParas(ct.ToString(), where);
            return DBHelper.GetTableByCondition(sql, where);
        }

        public object ExecDbFunScalar(string key, Hashtable where)
        {
            string sql = "SELECT fncontent FROM Sys0a80 WHERE fnkey=@fnkey";
            object ct = DBHelper.ExecuteScalar(sql, new SqlParameter("@fnkey", key));
            if (Validation.IsNull(ct)) return null;

            sql = ResetSqlAndParas(ct.ToString(), where);
            return DBHelper.DoSqlByCondition(sql, where);
        }

        /// <summary>
        /// 由于参数中可能存在特殊的情况，所以将SQL语句和参数集合充值一下
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        string ResetSqlAndParas(string sql, Hashtable where)
        {
            if (null == where) return sql;
            string temp = sql;

            string key;
            string rkey;
            string val;
            string newVal;
            List<object> keys = null;
            foreach (DictionaryEntry entry in where)
            {
                key = entry.Key.ToString();
                if (key.StartsWith("I%")) //SQL中有IN的情况
                {
                    rkey = key.Replace("I%", ""); //真实的参数名称
                    val = Validation.IsNull(entry.Value) ? "''" : entry.Value.ToString();
                    newVal = string.Concat(" ('", val.Replace(",", "','"), "') "); //自动加上双引号

                    temp = temp.Replace("@" + rkey, newVal).Replace(rkey, newVal);

                    if (keys == null)
                    {
                        keys = new List<object>();
                    }

                    keys.Add(entry.Key);
                }
            }


            if (null != keys) //移除条件
            {
                foreach (object k in keys)
                {
                    where.Remove(k);
                }
            }

            return temp;
        }
    }
}
